# src\core\orm_curd.py
from typing import Set, Any, Dict, List, Optional, Type, TypeVar, Tuple, Union
from sqlalchemy.orm import Session, Query
from pydantic import BaseModel
from src.core import retMes, str_utils as strU
from sqlalchemy import and_, or_, desc, asc
from sqlalchemy.inspection import inspect
from src.schemas.request_model import UpdateModel, UpdateItem, ensure_dict, DeleteModel, DeleteItem
from src.schemas.login_manager import AuthManager

T = TypeVar("T")  # SQLAlchemy Model
S = TypeVar("S", bound=BaseModel)  # Pydantic Schema


def _multi_filter(Model: Type[T], unique_fields: List[str], row: Dict[str, Any]):
    """根据多个字段生成 and_ 条件"""
    return and_(*(getattr(Model, k) == row[k] for k in unique_fields))


def Insert(
        db: Session,
        auth: AuthManager,
        Model: Type[T],
        Schema: Type[S],
        Model_Name: str,
        data: List[Dict[str, Any]],
        unique_fields: List[str],
        skip_duplicates: bool = True,
) -> Dict[str, Any]:  # Tuple[List[T], List[T]]:
    """
    通用批量写入/跳过重复

    :param db: SQLAlchemy Session
    :param auth:        登录信息
    :param Model: 数据库模型类
    :param Schema: Pydantic 校验模型（仅用于序列化返回）
    :param Model_Name:数据库模型类名称
    :param data: 待写入的字典列表
    :param unique_fields: 用于判断是否重复的唯一字段名列表
    :param skip_duplicates: True=跳过重复；False=抛异常
    :return: 按 retMes.Success 包装好的 dict
    """
    api_id = auth.api_id
    user_id = auth.user_id
    language = auth.language
    created, duplicates, faulty = [], [], []
    for row in data:
        if not isinstance(row, dict):
            row = row.model_dump()  #V1.dict(),V2 model_dump() # 不是 dict 转换dict结构
        #  --------- 新增：创建人、更新人等默认值 ---------
        if hasattr(Model, 'api_id'):
            row["api_id"] = api_id
        if hasattr(Model, 'api_id'):
            row["creator_by"] = user_id
        if hasattr(Model, 'last_updated_date'):
            row["last_updated_date"] = None
        if hasattr(Model, 'last_updated_by'):
            row["last_updated_by"] = None
        if hasattr(Model, 'deletion_date'):
            row["deletion_date"] = None
        if hasattr(Model, 'deletion_by'):
            row["deletion_by"] = None
        # --------- 新增：逻辑删除 ---------
        if hasattr(Model, 'deletion_mark'):
            if row["deletion_mark"]:
                if hasattr(Model, 'deletion_date') and row["deletion_date"] is None:
                    row["deletion_date"] = strU.now()
                if hasattr(Model, 'deletion_by'):
                    row["deletion_by"] = user_id

        # --------- 新增：完整性校验 ---------
        missing = [k for k in unique_fields if k not in row or row[k] is None]
        if missing:
            faultyData = {"message": f"行数据缺少唯一键字段为：{missing}",
                          "details": row}
            faulty.append(faultyData)

            continue

        # 1. 构造唯一性过滤条件
        exists = db.query(Model).filter(_multi_filter(Model, unique_fields, row)).first()
        # filter_cond = [getattr(Model, k) == row[k] for k in unique_fields]
        # exists = db.query(Model).filter(*filter_cond).first()
        if exists:
            duplicates.append(exists)
            if not skip_duplicates:
                retMes.Raise(message=f"Duplicate on {unique_fields}: {row}").mes()
                # raise ValueError(f"Duplicate on {unique_fields}: {row}")
            continue

        new_obj = Model(**row)
        db.add(new_obj)
        created.append(new_obj)

    if created:
        db.commit()
        for obj in created:
            db.refresh(obj)

    # 2. 统一序列化
    # created_dicts = [strU.model_to_dict(obj) for obj in created]
    # duplicates_dicts = [Schema.from_orm(obj).dict() for obj in duplicates]
    created_dicts = [Schema.model_validate(obj).model_dump() for obj in created]
    duplicates_dicts = [Schema.model_validate(obj).model_dump() for obj in duplicates]

    # 3. 构造响应
    message = None
    if created_dicts:
        message = retMes.Msg(db, api_id, '9610', language, len(created_dicts)).mes()
    if duplicates_dicts:
        msg = retMes.Msg(db, api_id, '9611', language, len(duplicates_dicts)).mes()
        if message:
            message += f"， {msg}"
        else:
            message = msg
    if faulty:
        msg = retMes.Msg(db, api_id, '9612', language, len(faulty)).mes()
        if message:
            message += f"， {msg}"
        else:
            message = msg
    response_data = {
        "created": {"count": len(created_dicts), "details": created_dicts},
        "duplicates": {"count": len(duplicates_dicts), "details": duplicates_dicts},
        "faulty": {"count": len(faulty), "details": faulty},
    }
    return retMes.Success(response_data, message).mes()


def query_with_page(
        db: Session,
        auth: AuthManager,
        model_cls: Type,  # 对应 models.SysUser
        resp_model_cls: Optional[Type] = None,  # 对应 schemas.SysUser，仅做字段校验，可省略
        *,
        id: Optional[str] = None,
        filter_model: Optional[BaseModel] = None,  # 对应 request_model.QueryModel
        page: int = 0,
        page_size: int = 100
) -> Dict[str, Any]:
    """
    通用分页查询
    :param db: 数据库会话
    :param auth: 登录信息
    :param model_cls: SQLAlchemy 实体类
    :param resp_model_cls: Pydantic 响应模型（可选）
    :param id: 按主键 id 精确查询
    :param filter_model: 前端传来的过滤 + 分页参数
    :param page: 默认页码
    :param page_size: 默认每页条数
    :return: 按 retMes.Success 包装好的 dict
    """
    api_id = auth.api_id
    language = auth.language
    # query: Query = db.query(model_cls).filter(model_cls.api_id == api_id)
    if hasattr(model_cls, 'api_id'):
        query: Query = db.query(model_cls).filter(model_cls.api_id == api_id)
    else:
        query: Query = db.query(model_cls)  # 不带 api_id 的表直接查全量
    # 1. 主键精确查询
    if strU.is_not_empty(id):
        query = query.filter(model_cls.id == id)
    # 2. 动态过滤条件
    elif strU.is_not_empty(filter_model):
        if filter_model.page is not None:
            page = filter_model.page
        if filter_model.page_size is not None:
            page_size = filter_model.page_size

        filter_data: Dict[str, Any] = filter_model.data or {}
        for key, value in filter_data.items():
            if hasattr(model_cls, key) and value is not None:
                query = query.filter(getattr(model_cls, key) == value)
        # 2. 处理排序
        if filter_model.sort:  # sort: List[str]
            order_clauses = []
            for col in filter_model.sort:
                # 支持 "-字段名" 表示倒序
                is_desc = col.startswith('-')
                field = col[1:] if is_desc else col
                if not hasattr(model_cls, field):
                    continue  # 非法字段直接忽略（或抛异常）
                column = getattr(model_cls, field)
                order_clauses.append(desc(column) if is_desc else asc(column))
            if order_clauses:
                query = query.order_by(*order_clauses)

    # 3. 分页
    def _calc_skip(pg: int | None, ps: int) -> int:
        pg = 1 if pg is None or pg == '' or not strU.is_number(pg) else int(pg)  # ← 空值保护
        return (pg - 1) * ps  # 注意：+1 已去掉，MySQL / PostgreSQL 从 0 开始

    if strU.is_empty(page) or strU.is_empty(page_size):
        total = query.count()
        rows = query.all()
    else:
        page = page or 1
        skip = _calc_skip(page, page_size)
        limit = page_size
        total = query.count()
        rows = query.offset(skip).limit(limit).all()

    # 4. 转 dict
    # details: List[Dict[str, Any]] = [strU.model_to_dict(r) for r in rows]
    # 4. 转 dict（带 resp_model_cls 校验）
    details: List[Dict[str, Any]] = []
    for row in rows:
        if resp_model_cls:  # ← 新增
            validated = resp_model_cls.from_orm(row)  # Pydantic 校验/脱敏
            details.append(validated.dict())
        else:  # ← 原有
            details.append(strU.model_to_dict(row))

    # 5. 包装
    payload = {
        "count": len(details),
        "page": page,
        "page_size": page_size,
        "total_count": total,
        "details": details
    }
    message = retMes.Msg(db, api_id, '9600', language, len(details)).mes()  #f'共计：{len(details)} 条数据'
    return retMes.Success(payload,
                          message,
                          record_count=len(details)).mes()


class PkValuesEmptyError(ValueError):
    """主键值列表为空异常"""
    pass


def _only_schema_fields(obj: Any, schema: Union[BaseModel, Type[BaseModel], dict]) -> Dict[str, Any]:
    """
    只保留 schema 中声明的字段（支持 Pydantic 模型或 dict）
    """

    if isinstance(schema, dict):
        schema_fields = schema.keys()
    elif isinstance(schema, BaseModel):
        schema_fields = schema.__fields_set__ | schema.__fields__.keys()
    else:  # 类类型
        schema_fields = schema.__fields__.keys()

    # 2. 统一把 obj 变 dict
    if isinstance(obj, dict):
        data = obj
    else:
        data = strU.model_to_dict(obj)  # 仅 ORM 对象才需要

    # 3. 过滤
    return {k: v for k, v in data.items() if k in schema_fields}


def updateItem(
        db: Session,
        auth: AuthManager,
        model_cls,
        pk_names: List[str],
        pk_values: Optional[List[Any]] = None,
        schema_obj: Union[BaseModel, Dict[str, Any]] = None,
        update_type: str = "update",
) -> Optional[Any]:
    """
    通用实体部分更新（支持联合主键）。
    如果调用方未传入 pk_values，则尝试从 schema_obj 解析；
    解析后仍为空则抛出 PkValuesEmptyError。


    :param db:          数据库会话
    :param auth:        登录信息
    :param model_cls:   ORM 模型类
    :param pk_names:    主键字段名列表，如 ['tenant_id', 'id']
    :param pk_values:   主键值列表，如 [1, 'abc']；可为 None
    :param schema_obj:  Pydantic 实例（含待更新字段）
    :param update_type:        修改类型
    :return:            更新后的 ORM 对象或 None（未找到）
    :raises:            PkValuesEmptyError
    """

    api_id = auth.api_id
    user_id = auth.user_id
    language = auth.language
    # 1. 若未显式传入 pk_values，则从 schema_obj 提取
    if strU.is_empty(pk_values):
        if schema_obj is None:
            message = retMes.Msg(db, api_id, '9210', language, 'schema_obj').mes()
            retMes.Raise(message=message).mes()
            # raise PkValuesEmptyError("schema_obj 为空，无法提取主键值")
        # data = schema_obj.dict()
        data = ensure_dict(schema_obj)
        pk_values = [data.get(name) for name in pk_names]

    # 2. 再次检查 pk_values 是否全部非空
    if not pk_values or any(v is None for v in pk_values):
        message = retMes.Msg(db, api_id, '9211', language).mes()
        retMes.Raise(message=message).mes()
        # raise PkValuesEmptyError("主键值列表为空或包含 None")

    # 3. 构造查询并更新
    # query = db.query(model_cls)
    if hasattr(model_cls, 'api_id'):
        query = db.query(model_cls).filter(model_cls.api_id == api_id)
    else:
        query = db.query(model_cls)  # 不带 api_id 的表直接查全量
    for name, value in zip(pk_names, pk_values):
        query = query.filter(getattr(model_cls, name) == value)
    # 如果update_type=“mark”，增加deletion_mark为0或者false判断
    if update_type == "mark":
        query = query.filter(model_cls.deletion_mark == 0)

    # 4. 先捞出所有主键（仅主键，节省内存）----------
    # 支持联合主键：每条主键是一个 tuple
    mapper = inspect(model_cls)
    pk_cols = mapper.primary_key
    if pk_cols is None:
        pk_cols = [getattr(model_cls, pk) for pk in pk_names]
    pk_list = query.with_entities(*pk_cols).all()  # [(val1, val2), ...]

    if not pk_list:
        return []

    # 5. 组装更新字段
    update_data = ensure_dict(schema_obj)
    update_data = {
        k: v for k, v in update_data.items()
        if k not in pk_names and k not in {"create_date", "creator_by"}
    }
    if hasattr(model_cls, "last_updated_date"):
        update_data["last_updated_date"] = strU.now()
    if hasattr(model_cls, "last_updated_by"):
        update_data["last_updated_by"] = user_id

    if not update_data:  # 无字段可更新
        return []
    print(update_data)
    # 6. 批量更新
    query.update(update_data, synchronize_session=False)
    db.commit()
    #  7. 根据主键再查一次，拿到更新后实体 ----------
    # 动态构造 OR (pk1=val1 AND pk2=val2) ...
    or_conditions = [
        and_(*[col == val for col, val in zip(pk_cols, vals)])
        for vals in pk_list
    ]
    updated_rows = db.query(model_cls).filter(or_(*or_conditions)).all()
    # 8. 按 schema_obj 字段过滤
    if schema_obj is None:
        return [strU.model_to_dict(r) for r in updated_rows]
    return [_only_schema_fields(r, schema_obj) for r in updated_rows]


def update(
        db: Session,
        auth: AuthManager,
        model_cls,
        update_obj: Optional[UpdateModel] = None,
) -> Dict[str, Any]:
    """
    通用实体部分更新（支持联合主键）。
    如果调用方未传入 pk_values，则尝试从 schema_obj 解析；
    解析后仍为空则抛出 PkValuesEmptyError。

    :param db:          数据库会话
    :param auth:        登录信息
    :param model_cls:   ORM 模型类
    :param update_obj:  Pydantic 实例（含待更新字段）
    :return:            更新后的 ORM 对象或 None（未找到）
    :raises:            PkValuesEmptyError
    """
    # 1.
    api_id = auth.api_id
    language = auth.language
    if update_obj is None:
        message = retMes.Msg(db, api_id, '9212', language, 'update_obj').mes()
        return retMes.Error(message).mes()

    updated_entities: List[Any] = []
    # 遍历裸数组
    for item in update_obj.root:
        # 如果 item 里给了 pk_names / pk_values，就用它；否则用外层默认值

        _pk_names = item.pk_names if item.pk_names is not None else None
        _pk_values = item.pk_values if item.pk_values is not None else None
        _data = item.data if item.data is not None else None
        entity = updateItem(
            db=db,
            auth=auth,
            model_cls=model_cls,
            pk_names=_pk_names,
            pk_values=_pk_values,
            schema_obj=_data,  # 把 data 字典作为 schema_obj
        )
        if entity is not None:
            updated_entities.extend(entity)  # entity_dict
            # entity_dict = strU.model_to_dict(entity)
            # append(obj)  把  obj  原封不动当成一个元素追加到列表末尾。 结果长度 +1
            # extend(iterable)  #  把  iterable  拆开成单个元素后再依次追加。  #  结果长度 +len(iterable)
            # updated_entities.append(entity_dict)
    message = retMes.Msg(db, api_id, '9600', language, len(updated_entities)).mes()
    return retMes.Success(updated_entities,
                          message,
                          record_count=len(updated_entities)).mes()


def deleteItem(
        db: Session,
        auth: AuthManager,
        model_cls,
        pk_names: List[str],
        pk_values: Optional[List[Any]] = None,
        schema_obj: Union[BaseModel, Dict[str, Any]] = None,
        exclude_fields: Optional[Set[str]] = {"api_id", "creator_by"},
) -> List[Dict[str, Any]]:
    """
    删除所有符合主键条件的记录（支持联合主键）
    返回实际删除的行数
    """
    api_id = auth.api_id
    language = auth.language
    # 1. 解析主键
    if strU.is_empty(pk_values):
        data = ensure_dict(schema_obj)
        pk_values = [data.get(name) for name in pk_names]
    if not pk_values or any(v is None for v in pk_values):
        message = retMes.Msg(db, api_id, '9211', language).mes()
        retMes.Raise(message=message).mes()

    # 2. 构造条件
    # query = db.query(model_cls)
    if hasattr(model_cls, 'api_id'):
        query: Query = db.query(model_cls).filter(model_cls.api_id == api_id)
    else:
        query: Query = db.query(model_cls)  # 不带 api_id 的表直接查全量

    for name, value in zip(pk_names, pk_values):
        query = query.filter(getattr(model_cls, name) == value)
    # 3. 先取出字段（避免 detached 后访问失败）
    rows = query.all()
    if not rows:
        return []
    data_list = strU.model_to_dict(rows)
    # 3.2 统一剔除
    if exclude_fields:
        for item in data_list:
            for f in exclude_fields:
                item.pop(f, None)

    # 3. 批量删除（0 查询）
    row_count = query.delete(synchronize_session=False)  # 直接发 DELETE ... WHERE ...
    db.commit()
    return data_list


def delete(
        db: Session,
        auth: AuthManager,
        model_cls,
        delete_obj: Optional[DeleteModel] = None,
) -> Dict[str, Any]:
    """
    通用实体部分更新（支持联合主键）。
    如果调用方未传入 pk_values，则尝试从 schema_obj 解析；
    解析后仍为空则抛出 PkValuesEmptyError。

    :param db:          数据库会话
    :param auth:        登录信息
    :param model_cls:   ORM 模型类
    :param delete_obj:  Pydantic 实例（含待更新字段）
    :return:            更新后的 ORM 对象或 None（未找到）
    :raises:            PkValuesEmptyError
    """
    api_id = auth.api_id
    language = auth.language
    # 1.
    if delete_obj is None:
        message = retMes.Msg(db, api_id, '9212', language, 'delete_obj').mes()
        return retMes.Error(message).mes()

    deleted_entities: List[Any] = []
    marked_entities: List[Any] = []
    # 遍历裸数组
    for item in delete_obj.root:
        # 如果 item 里给了 pk_names / pk_values，就用它；否则用外层默认值
        _deletion_model = item.deletion_model if item.deletion_model is not None else None
        _pk_names = item.pk_names if item.pk_names is not None else None
        _pk_values = item.pk_values if item.pk_values is not None else None
        _data = item.data if item.data is not None else None
        _deletion_reason = item.deletion_reason if item.deletion_reason is not None else None
        if _deletion_model == "mark":
            #增加Data内容，若没有，则增加一个
            if _data is None:
                if _pk_names is None:
                    message = retMes.Msg(db, api_id, '9211', language).mes()
                    retMes.Raise(message=message).mes()
                _data = {"deletion_reason": _deletion_reason
                         }
            else:
                # 检查_data中是否有deletion_reason字段
                if "deletion_reason" not in _data:
                    _data["deletion_reason"] = _deletion_reason
            # 增加deletion_date字段与强制增加删除日期
            _data["deletion_date"] = strU.now()
            _data["deletion_mark"] = 1
            # 标记删除
            marked = updateItem(
                db=db,
                auth=auth,
                model_cls=model_cls,
                pk_names=_pk_names,
                pk_values=_pk_values,
                schema_obj=_data,  # 把 data 字典作为 schema_obj
                update_type="mark"
            )

            if marked is not None and len(marked) > 0:
                marked_entities.extend(marked)
                # print(marked)
                # marked_dict = strU.model_to_dict(marked)
                # marked_entities.extend(marked_dict)
        else:
            deleted = deleteItem(
                db=db,
                auth=auth,
                model_cls=model_cls,
                pk_names=_pk_names,
                pk_values=_pk_values,
                schema_obj=_data,  # 把 data 字典作为 schema_obj
            )
            if len(deleted) > 0:
                deleted_entities.extend(deleted)
            """
            if deleted is not None and len(deleted)>0:
                deleted_dict = strU.model_to_dict(deleted)
                deleted_entities.extend(deleted_dict)
            """

    # 3. 构造响应
    message_parts = []
    if deleted_entities:
        message = retMes.Msg(db, api_id, '9630', language, len(deleted_entities)).mes()
        message_parts.append(message)
    if marked_entities:
        message = retMes.Msg(db, api_id, '9631', language, len(marked_entities)).mes()
        message_parts.append(message)
    message = retMes.Msg(db, api_id, '9601', language).mes()
    retMessage = "，".join(message_parts) or message

    response_data = {
        "deleted": {"count": len(deleted_entities), "details": deleted_entities},
        "marked": {"count": len(marked_entities), "details": marked_entities},
    }
    record_count = len(deleted_entities) + len(marked_entities)

    return retMes.Success(response_data,
                          f'{retMessage} ',
                          record_count).mes()
